package org.example.springboot.mapper;

import org.apache.ibatis.annotations.*;
import org.example.springboot.entity.Order;

import java.util.List;

@Mapper
public interface OrderMapper {

    @Select("SELECT o.id, u.name AS parentName, o.course, t.name AS teacherName,o.content , o.user_account, o.teacher_account,o.order_date, o.salary, o.duration_begin, o.duration_end, o.status " +
            "FROM orders o " +
            "JOIN need n ON o.user_account = n.account " +
            "JOIN user u ON n.account = u.account " +
            "JOIN teacher t ON o.teacher_account = t.account " +
            "WHERE u.name LIKE CONCAT('%', #{name}, '%') " +
            "AND t.name LIKE CONCAT('%', #{teachername}, '%') " +
            "LIMIT #{offset}, #{pageSize}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "parentName"),
            @Result(property = "course", column = "course"),
            @Result(property = "teachername", column = "teacherName"),
            @Result(property = "order_date", column = "order_date"),
            @Result(property = "salary", column = "salary"),
            @Result(property = "duration", column = "duration")
    })

    List<Order> findByParentAndTeacher(@Param("name") String name,
                                       @Param("teachername") String teachername,
                                       @Param("offset") int offset,
                                       @Param("pageSize") int pageSize);

    @Select("SELECT o.id, u.name AS parentName, o.course, t.name AS teacherName, o.content, o.user_account, o.teacher_account,o.order_date, o.salary,  o.duration_begin, o.duration_end " +
            "FROM orders o " +
            "JOIN need n ON o.user_account = n.account " +
            "JOIN user u ON n.account = u.account " +
            "JOIN teacher t ON o.teacher_account = t.account " +
            "WHERE u.name LIKE CONCAT('%', #{name}, '%') " +
            "LIMIT #{offset}, #{pageSize}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "parentName"),
            @Result(property = "course", column = "course"),
            @Result(property = "teachername", column = "teacherName"),
            @Result(property = "order_date", column = "order_date"),
            @Result(property = "salary", column = "salary"),
            @Result(property = "duration", column = "duration")
    })
    List<Order> findByParent(@Param("name") String name,
                             @Param("offset") int offset,
                             @Param("pageSize") int pageSize);

    @Select("SELECT o.id, u.name AS parentName, o.course, t.name AS teacherName, o.content, o.user_account, o.teacher_account, o.order_date, o.salary, o.duration_begin, o.duration_end " +
            "FROM orders o " +
            "JOIN need n ON o.user_account = n.account " +
            "JOIN teacher t ON o.teacher_account = t.account " +
            "JOIN user u ON n.account = u.account " +
            "WHERE t.name LIKE CONCAT('%', #{teachername}, '%') " +
            "LIMIT #{offset}, #{pageSize}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "parentName"),
            @Result(property = "course", column = "course"),
            @Result(property = "teachername", column = "teacherName"),
            @Result(property = "order_date", column = "order_date"),
            @Result(property = "salary", column = "salary"),
            @Result(property = "duration", column = "duration")
    })
    List<Order> findByTeacher(@Param("teachername") String teachername,
                              @Param("offset") int offset,
                              @Param("pageSize") int pageSize);



    @Select("SELECT COUNT(*) " +
            "FROM orders o " +
            "JOIN need n ON o.user_account = n.account " +
            "JOIN user u ON n.account = u.account " +
            "JOIN teacher t ON o.teacher_account = t.account " +
            "WHERE u.name LIKE CONCAT('%', #{name}, '%') " +
            "AND t.name LIKE CONCAT('%', #{teachername}, '%')")
    int countByParentAndTeacher(@Param("name") String name, @Param("teachername") String teachername);


    @Select("SELECT COUNT(*) " +
            "FROM orders o " +
            "JOIN need n ON o.user_account = n.account " +
            "JOIN user u ON n.account = u.account " +
            "WHERE u.name LIKE CONCAT('%', #{name}, '%')")
    int countByParent(@Param("name") String name);


    @Select("SELECT COUNT(*) " +
            "FROM orders o " +
            "JOIN need n ON o.user_account = n.account " +
            "JOIN teacher t ON o.teacher_account = t.account " +
            "WHERE t.name LIKE CONCAT('%', #{teachername}, '%')")
    int countByTeacher(@Param("teachername") String teachername);

    @Insert("INSERT INTO orders(user_account, teacher_account, course, order_date, content,duration_begin, duration_end, salary, status) " +
            "VALUES(#{user_account}, #{teacher_account}, #{course}, NOW(), #{content}, #{duration_begin}, #{duration_end}, #{salary}, #{status})")
    void createOrder(Order order);

    @Select("SELECT * FROM orders WHERE user_account = #{user_account} AND teacher_account = #{teacher_account} AND course = #{course}")
    Order SelectByAll (String user_account, String teacher_account, String course);

    @Select("SELECT o.*, u.name, t.name AS teachername, c.avatar FROM orders o JOIN user u ON o.user_account = u.account JOIN teacher t on o.teacher_account = t.account JOIN classification c ON o.course = c.course WHERE o.teacher_account = #{teacher_account}")
    List<Order> findAllOrders(String teacher_account);

    @Select("SELECT o.*, u.name, t.name AS teachername, c.avatar FROM orders o JOIN user u ON o.user_account = u.account JOIN teacher t on o.teacher_account = t.account JOIN classification c ON o.course = c.course WHERE o.user_account = #{user_account}")
    List<Order> findAllOrdersByUser(String user_account);

    @Update("UPDATE orders SET status = '1' WHERE id = #{id}")
    void updateOrderStatus(int id);

    @Update("UPDATE orders SET status = '2', duration_end = NOW() WHERE id = #{id}")
    void updateOrderDuration(int id);

    @Delete("DELETE FROM orders WHERE id = #{id}")
    void deleteOrder(int id);

    @Select("SELECT o.*, u.name, t.name AS teachername, c.avatar FROM orders o JOIN user u ON o.user_account = u.account JOIN teacher t on o.teacher_account = t.account JOIN classification c ON o.course = c.course WHERE o.id = #{id}")
    Order selectOrder(int id);
}
